/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package models.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import models.DBHandler;
import models.beans.Event;

/**
 *
 * @author Hoang Dong Tien
 */
public class EventDAO {

    public static List<Event> getAllEventsByCondition(String whereClause) throws ClassNotFoundException, SQLException {
        List<Event> events = new ArrayList<Event>();
        ResultSet rs = null;
        Connection oconn = DBHandler.getConnection();
        StringBuilder sb = new StringBuilder("SELECT * FROM [Events] ");
        if(whereClause != null){
            sb.append(whereClause);
        }
        PreparedStatement pstmt = oconn.prepareStatement(sb.toString());
        rs = pstmt.executeQuery();

        Event eventTmp = null;
        while(rs.next()){
            eventTmp = new Event();
            eventTmp.setEventId(rs.getInt("EventId"));
            eventTmp.setDescription(rs.getNString("Description"));
            eventTmp.setEndDate_sql(rs.getDate("EndDate"));
            eventTmp.setEventImage(rs.getNString("EventImage"));
            eventTmp.setEventName(rs.getNString("EventName"));
            eventTmp.setEventType(rs.getNString("EventType"));
            eventTmp.setStartDate_sql(rs.getDate("StartDate"));
            eventTmp.setCreateUserId(rs.getInt("CreatedUserId"));
            events.add(eventTmp);
        }

        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);

        return events;
    }

    
    public static List<Event> getTopEventsByCondition(String whereClause) throws ClassNotFoundException, SQLException {
        List<Event> events = new ArrayList<Event>();
        ResultSet rs = null;
        Connection oconn = DBHandler.getConnection();
        StringBuilder sb = new StringBuilder("SELECT TOP 5 * FROM [Events] ");
        if(whereClause != null){
            sb.append(whereClause);
        }
        PreparedStatement pstmt = oconn.prepareStatement(sb.toString());
        rs = pstmt.executeQuery();

        Event eventTmp = null;
        while(rs.next()){
            eventTmp = new Event();
            eventTmp.setEventId(rs.getInt("EventId"));
            eventTmp.setDescription(rs.getNString("Description"));
            eventTmp.setEndDate_sql(rs.getDate("EndDate"));
            eventTmp.setEventImage(rs.getNString("EventImage"));
            eventTmp.setEventName(rs.getNString("EventName"));
            eventTmp.setEventType(rs.getNString("EventType"));
            eventTmp.setStartDate_sql(rs.getDate("StartDate"));
            eventTmp.setCreateUserId(rs.getInt("CreatedUserId"));
            events.add(eventTmp);
        }

        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);

        return events;
    }

    public static int addNewEvent(Event newevent) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("INSERT INTO [BluePumpkin].[dbo].[Events]([EventName] ,[EventType] ,[StartDate] ,[EndDate] ,[Description] ,[EventImage] ,[CreatedUserId]) VALUES(?,?,?,?,?,?,?)");
        pstmt.setNString    (1, newevent.getEventName());
        pstmt.setNString    (2, newevent.getEventType());
        pstmt.setDate       (3, newevent.getStartDate_sql());
        pstmt.setDate       (4, newevent.getEndDate_sql());
        pstmt.setNString    (5, newevent.getDescription());
        pstmt.setNString    (6, newevent.getEventImage());
        pstmt.setInt        (7, newevent.getCreateUserId());
        
        result = pstmt.executeUpdate();


        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }

    public static int updateEvent(Event newevent) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("UPDATE [BluePumpkin].[dbo].[Events] SET [EventName] = ?,[EventType] = ?,[StartDate] = ?,[EndDate] = ?,[Description] = ?,[EventImage] = ?,[CreatedUserId] = ? WHERE  [EventId]=?");
        pstmt.setNString(1, newevent.getEventName());
        pstmt.setNString(2, newevent.getEventType());
        pstmt.setDate(3, newevent.getStartDate_sql());
        pstmt.setDate(4, newevent.getEndDate_sql());
        pstmt.setNString(5, newevent.getDescription());
        pstmt.setNString(6, newevent.getEventImage());
        pstmt.setInt(7, newevent.getCreateUserId());
        pstmt.setInt(8, newevent.getEventId());
        
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    
    public static int deleteEvent(int eventId) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("DELETE FROM [Events] WHERE EventId=?");
        pstmt.setInt(1, eventId);
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    
    public static int deleteEvent(Event event) throws ClassNotFoundException, SQLException {
        return deleteEvent(event.getEventId());
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        List<Event> events = getAllEventsByCondition(null);
        for (Event event : events){
            System.out.println("Event Name : " + event.getEventName());
        }
    }
}
